package com.cloud.manage.config;

import cn.hutool.core.date.DateUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.Iterator;
import java.util.List;

@Slf4j
@Component
public class ConnectionSuport {
	public Connection conn;
	public Statement st;
	public PreparedStatement ps;

	@Value("spring.datasource.url")
	public String url;

	@Value("spring.datasource.username")
	public String username;

	@Value("spring.datasource.password")
	public String password;
	public Connection getJDBCConnection() throws Exception {
		Class.forName("org.postgresql.Driver");
		conn = DriverManager.getConnection(url, username, password);
		conn.setAutoCommit(false);
		return conn;
	}

	public void delete(String tableName) throws Exception {
		String sql = "truncate table " + tableName;
		conn = getJDBCConnection();
		st = conn.createStatement();
		st.execute(sql);
		conn.commit();
		close(null, st, conn);
	}

	public int insert(String sql, List<Object[]> data) throws Exception {
		conn = getJDBCConnection();
		int ret = 0;
		try {
			ps = conn.prepareStatement(sql);
			Iterator<Object[]> it = data.iterator();
			Object[] objs;
			int i, j = 0;
			while (it.hasNext()) {
				objs = it.next();
				i = 0;
				for (Object o : objs) {
					if (o == null) {
						ps.setNull(++i, Types.DOUBLE);
						continue;
					}
					if (o instanceof Date) {
						ps.setObject(++i, o, Types.DATE);
					} else {
						ps.setObject(++i, o);
					}
				}
				ps.addBatch();
				if ((++j) % 3000 == 0) {
					ps.executeBatch();
					ps.clearBatch();
				}
			}
			ps.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			log.info("在执行update方法时出现异常！");
			e.printStackTrace();
		} finally {
			close(null, ps, conn);// 释放资源
		}
		return ret;
	}

	public void update(String tableName, List<Object[]> datas)
			throws Exception {
		if (datas.isEmpty()) {
			log.info("没有数据");
			return;
		}
		// 获取数据库连接
		try (Connection conn = getJDBCConnection()) {
			// 禁用自动提交，手动控制事务
			conn.setAutoCommit(false);
			try {
				// 先删除数据
				String sql = "delete from " + tableName + " where pubtime < '"+ DateUtil.format(DateUtil.offsetDay(DateUtil.date(),-7),"yyyyMMddHHmmss") +"'";
				st = conn.createStatement();
				st.execute(sql);
//				String sql = "truncate table " + tableName;
//				st = conn.createStatement();
//				st.execute(sql);
				// 构建插入SQL
				String yw = "";
				if (datas.size() == 0) {
					log.info("没有数据");
					return;
				}
				for (Object o : datas.get(0)) {
					yw += "?,";
				}
				yw = yw.substring(0, yw.length() - 1);
				String insert = "insert into " + tableName + " values(" + yw + ")";
				ps = conn.prepareStatement(insert);
				Iterator<Object[]> it = datas.iterator();
				Object[] objs;
				int i, j = 0;
				while (it.hasNext()) {
					objs = it.next();
					i = 0;
					for (Object o : objs) {
						if (o == null) {
							ps.setNull(++i, Types.DOUBLE);
							continue;
						}
						if (o instanceof Date) {
							ps.setObject(++i, o, Types.DATE);
						} else {
							ps.setObject(++i, o);
						}
					}
					ps.addBatch();
				}
				ps.executeBatch();
				// 提交事务
				conn.commit();
			} catch (Exception e) {
				// 发生异常时回滚事务
				conn.rollback();
				throw e;  // 重新抛出异常，供外部处理
			}finally {
			// 确保在事务结束后恢复自动提交状态
			if (conn != null) {
				try {
					conn.setAutoCommit(true);
					close(null, ps, conn);// 释放资源
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		}
	}

	public void close(ResultSet rs, Statement st, Connection con) {
		try {
			if (rs != null)
				rs.close();
			if (st != null)
				st.close();
			if (con != null)
				con.close();
		} catch (SQLException e) {
			log.info("在释放资源时出现异常！");
			e.printStackTrace();
		}
	}

	public void insertSQL(String tablename, List<String> sql)
			throws Exception {
		conn = getJDBCConnection();
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			for (int i = 0; i < sql.size(); i++) {
				stmt.addBatch(sql.get(i));
				if (i % 100 == 0) {
					stmt.executeBatch();
					stmt.clearBatch();
				}
			}
			stmt.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			log.info("批量插入时出现异常！");
			e.printStackTrace();
		} finally {
			close(null, stmt, conn);// 释放资源
		}
	}
}
